USE [master]
GO

CREATE DATABASE [mediaPortal] 
GO

USE [mediaPortal]
GO


CREATE TABLE [Category]
(
	[ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] NVARCHAR(50) NOT NULL,
	[Description] NVARCHAR(MAX) NOT NULL
 )  
GO

CREATE TABLE [Comments]
(
	[ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[NewsID] INT NOT NULL,
	[Title] NVARCHAR(100) NOT NULL,
	[Text] NVARCHAR(max) NOT NULL,
	[UserID] INT NOT NULL,
	[PublishDate] DATETIME NULL
)	

GO

CREATE TABLE [ImageNewsBind]
(
	[ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[NewsID] INT NOT NULL,
	[Url] NVARCHAR(300) NOT NULL,
	[Name] NVARCHAR(100) NOT NULL
)	

GO

CREATE TABLE [ImageUsersBind]
(
	[ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[UserID] INT NOT NULL,
	[Name] NVARCHAR(200) NOT NULL,
	[Url] NVARCHAR(300) NOT NULL
)	

GO

CREATE TABLE [News]
(
	[ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Title] NVARCHAR(50) NOT NULL,
	[Category] INT NULL,
	[Author] NVARCHAR(50) NOT NULL,
	[Text] NVARCHAR(MAX) NOT NULL,
	[PublishDate] DATETIME NULL,
	[UpdateDate] DATETIME NULL
 )	

GO

CREATE TABLE [NewsRating]
(
	[NewsId] INT NOT NULL,
	[Rating] DECIMAL(2, 1) NOT NULL,
	[Votes] INT NOT NULL
)

GO

CREATE TABLE [PrivateMessage]
(
	[ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Title] NVARCHAR(150) NOT NULL,
	[Text] NVARCHAR(max) NOT NULL,
	[SenderID] INT NOT NULL,
	[ReceiverID] INT NOT NULL,
	[SendDate] DATETIME NOT NULL
)	

GO

CREATE TABLE [Tags]
(
	[ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] NVARCHAR(50) NOT NULL,
)	
GO

CREATE TABLE [TagsBind]
(
	[TagID] INT NOT NULL,
	[NewsID] INT NOT NULL
)

GO


CREATE TABLE [Users]
(
	[ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Login] NVARCHAR(50) NOT NULL,
	[Role] INT NOT NULL,
	[Pwd] NVARCHAR(50) NOT NULL,
	[Email] NVARCHAR(60) NOT NULL,
	[Name] NVARCHAR(50) NOT NULL,
	[FName] NVARCHAR(50) NULL,
	[MName] NVARCHAR(50) NULL,
	[LastVisit] DATETIME NULL
)	

GO

CREATE TABLE [UsersVotes]
(
	[UserID] INT NOT NULL,
	[NewsID] INT NOT NULL,
	[Rating] INT NOT NULL
)

GO
---------------------------------------

CREATE PROCEDURE [AddCategory]
	@Name NVARCHAR(50),
	@Description NVARCHAR(MAX)
AS
Begin
	INSERT INTO 
	[Category]
	(
		Name,
		Description
	)
	VALUES
	(
		@Name,
		@Description
	)
END


GO

CREATE PROCEDURE [AddComment]
	@NewsID INT,
	@UserLogin NVARCHAR(50),
	@Title NVARCHAR(50),
	@Text NVARCHAR(MAX),
	@PublishDate DATETIME
AS
BEGIN
	DECLARE @UserID INT
	SET @UserID = (	SELECT 
						ID 
					FROM 
						[Users] 
					WHERE 
						Login=@UserLogin
				   )
	
	
	INSERT INTO
	[Comments]
	(
		NewsID,
		UserID,
		Title,
		Text,
		PublishDate	
	)VALUES
	(
		@NewsID,
		@UserID,
		@Title,
		@Text,
		@PublishDate
	)
END

GO

CREATE PROCEDURE [AddNews] 
	@Title NVARCHAR(50), 
	@Category INT,
	@Author NVARCHAR(50),
	@Text NVARCHAR(MAX),
	@PublishDate DATETIME = NULL,
	@UpdateDate DATETIME = NULL

AS
BEGIN

	INSERT INTO 
	[News] 
	(
		Title,
		Category,
		Author,
		Text,
		PublishDate,
		UpdateDate
	) 
	VALUES 
	(
		@Title,
		@Category,
		@Author,
		@Text,
		@PublishDate,
		@UpdateDate
	)
	SELECT scope_IDentity()
END

GO


CREATE PROCEDURE [AddTag]
	@Name NVARCHAR(50)
AS
BEGIN
	

	INSERT INTO 
	[Tags]
	(
		Name
	)
	VALUES
	(
		@Name
	)
END

GO

CREATE PROCEDURE .[AddTagsBind]
	@TagID INT,
	@NewsID INT 
AS
BEGIN
	
	INSERT INTO 
	[TagsBind] 
	(
		TagID,
		NewsID
	) 
	VALUES
	(
		@TagID,
		@NewsID
	)
	
		
END

GO

CREATE PROCEDURE [AddUser] 
	@login NVARCHAR(50), 
	@Role INT = 3,
	@Pwd NVARCHAR(50),
	@Email NVARCHAR(60),
	@Name NVARCHAR(50),
	@FName NVARCHAR(50) = NULL,
	@MName NVARCHAR(50) = NULL

AS
BEGIN

	DECLARE @USER NVARCHAR(50)
	SET @USER = (	SELECT 
						ID 
					FROM 
						[Users] 
					WHERE 
						Login = @Login
				 )
	
	IF @USER IS NULL
	BEGIN
		INSERT INTO 
		[Users]
		(
			Login,
			Role,
			Pwd,
			Email,
			Name,
			FName,
			MName,
			LastVisit
		
		) VALUES
		(
			@Login,
			@Role,
			@Pwd,
			@Email,
			@Name,
			@FName,
			@MName,
			GETDATE()
		)
		SELECT scope_IDentity()
	END
END

GO


CREATE PROCEDURE .[DeleteCategory]
	@ID INT
AS
BEGIN

	UPDATE
		[News]
	SET 
		[Category] = 0
	WHERE
		[Category] = @ID
	
	DELETE FROM 
		[Category]
	WHERE
		[ID] = @ID
      
END

GO

CREATE PROCEDURE [DeleteComment]
	@ID INT
AS
BEGIN
	
	DELETE
	FROM
	[Comments]
	WHERE
		ID=@ID
END
GO

CREATE PROCEDURE .[DeleteNews] 
	@ID INT
AS
BEGIN

	DELETE 
	FROM 
		[TagsBind]
	WHERE
		NewsID=@ID
	
	DELETE 
	FROM 
		[News] 
	WHERE 
		ID=@ID	
		
END
GO

CREATE PROCEDURE [DeleteTag] 
	@ID INT
AS
BEGIN
	
	DELETE
	FROM 
		[TagsBind]
	WHERE
		TagID=@ID
	
	DELETE 
	FROM 
		[Tags] 
	WHERE 
		ID = @ID 
END

GO

CREATE PROCEDURE [DeleteTagsBind]
	@NewsID INT
AS
BEGIN
	
	DELETE
	FROM
		[TagsBind]
	WHERE
		NewsID = @NewsID
	
END
GO

CREATE PROCEDURE [DeleteUser] 
	@ID INT 

AS
BEGIN

	DELETE
	FROM 
		[Users] 
	WHERE 
		ID=@ID AND
		Role <> 1
END

GO

CREATE PROCEDURE [DeleteNewsImage] 
	@ID INT
AS
BEGIN

	DELETE
	FROM
		[ImageNewsBind]
	WHERE
		ID = @ID
END

GO

CREATE PROCEDURE [EditComment] 
	@ID INT,
	@Text NVARCHAR(MAX),
	@Title NVARCHAR(50)
AS
BEGIN
	UPDATE
		[Comments]
	SET
		Text=@Text,
		Title=@Title
	WHERE
		ID=@ID
END

GO

CREATE PROCEDURE [GetCategories] 
	@ID INT = NULL,
	@Name NVARCHAR(50) = NULL,
	@OrderBy NVARCHAR(50) = 'Name'
AS
BEGIN
	
	SELECT 
		* 
	FROM 
		[Category] C
	WHERE
		(	C.[ID] = @ID OR
			@ID IS NULL OR
			@ID = 0
		 ) AND
		 
		(	C.[Name] = @Name OR
			@Name IS NULL
		)
	ORDER BY
		CASE WHEN @OrderBy = 'ID' THEN [ID] END ,
		CASE WHEN @orderBy = 'Name' THEN [Name] END
	
END

GO

CREATE PROCEDURE [GetComments]
	@NewsID INT = NULL,
	@PublishDate DATETIME = NULL,
	@UserID INT = NULL,
	@ID INT = NULL
AS
BEGIN
	
	SELECT
		*
	FROM
		[Comments] C
	WHERE
	(	C.[NewsID] =  @NewsID OR
		@NewsID IS NULL OR
		@NewsID = 0
	) AND 
	
	(	C.[UserID] = @UserID OR
		@UserID IS NULL OR
		@UserID = 0
	) AND
	
	(	C.[PublishDate] = @PublishDate OR
		@publishDate IS NULL
	) AND
	
	(	C.[ID] = @ID OR
		@ID IS NULL OR
		@ID = 0
	)
END

GO

CREATE PROCEDURE [GetNews] 
	@ID INT = NULL,
	@Category INT = NULL,
	@Title NVARCHAR(50) = NULL,
	@Author NVARCHAR (50) = NULL,
	@Text NVARCHAR(50) = NULL,
	@orderBy NVARCHAR(50) = 'publishDate',
	@TagID INT = NULL,
	@TagName NVARCHAR(50) = NULL,
	@PublishDate INT = NULL
AS
BEGIN
		
	IF @TagName IS NOT NULL 
	BEGIN
		
	SELECT 
      N.ID,
      Title,
      Category,
      Author,
      Text,
      publishDate,
      updateDate,
      TagID,
      Tags.Name
	FROM
		[News] N inner join 
		[TagsBind] on N.ID = [TagsBind].NewsID inner join 
		[Tags] on [Tags].ID=[TagsBind].TagID
	WHERE
		(	N.[Author] = @Author OR	
			@Author IS NULL
		) AND
		
		(	N.[ID] = @ID OR
			@ID IS NULL OR 
			@ID=0
		) AND
		
		(	N.[Category] = @Category OR 
			@Category IS NULL OR 
			@Category=0
		) AND
		
		(	N.[Title] LIKE '%'+@Title+'%' OR
			@Title IS NULL
		) AND
		
		(	N.[Text] LIKE '%'+@Text+'%' OR 
			@Text IS NULL
		) AND
		
		(	TagsBind.TagID = @TagID OR 
			@TagID IS NULL
		) AND
		
		(	Tags.Name = @TagName OR 
			@TagName IS NULL
		)
	ORDER BY
		CASE WHEN @orderBy = 'Title' THEN [Title] END ,
		CASE WHEN @orderBy = 'publishDate' THEN [publishDate] END ,
		CASE WHEN @orderBy = 'updateDate' THEN [updateDate] END,
		CASE WHEN @orderBy = 'Author' THEN [Author] END 
			
	END
	ELSE
	BEGIN
	
	SELECT 
      *
	FROM
		[News] N 
	WHERE
		(	N.[Author] = @Author OR 
			@Author IS NULL
		) AND
		
		(	N.[ID] = @ID OR 
			@ID IS NULL OR 
			@ID=0
		) AND
		
		(	N.[Category] = @Category OR 
			@Category IS NULL OR 
			@Category=0
		) AND
		
		(	N.[Title] LIKE '%'+@Title+'%' OR 
			@Title IS NULL
		) AND
		
		(	N.[Text] LIKE '%'+@Text+'%' OR 
			@Text IS NULL
		) AND
		
		(	@PublishDate = 0 OR 
			N.[PublishDate] IS NOT NULL
		)
	ORDER BY
		CASE WHEN @orderBy = 'Title' THEN [Title] END ,
		CASE WHEN @orderBy = 'publishDate' THEN [publishDate]  END DESC,
		CASE WHEN @orderBy = 'updateDate' THEN [updateDate] END,
		CASE WHEN @orderBy = 'Author' THEN [Author] END 
	END
	
END

GO

CREATE PROCEDURE [GetNewsImages]
	@NewsID INT = NULL,
	@ID INT = NULL
AS
BEGIN
	
	SELECT
		*
	FROM
		[ImageNewsBind] I
	WHERE
		(	I.[NewsID] = @NewsID OR 
			@NewsID IS NULL
		) AND
		
		(	I.[ID] = @ID OR 
			@ID IS NULL
		)
	ORDER BY
		Name
END

GO

CREATE PROCEDURE [GetNewsImagesUrl]
	@NewsID INT
	
AS
BEGIN
	SELECT
		*
	FROM
		[ImageNewsBind] I
	WHERE
		(	I.[NewsID]  = @NewsID OR 
			@NewsID IS NULL
		)
	ORDER BY Name
END

GO

CREATE PROCEDURE [GetRating] 
	@NewsID INT
AS
BEGIN
	SELECT
		Rating
	FROM
		[NewsRating]
	WHERE
		NewsID = @NewsID
END

GO

CREATE PROCEDURE [GetTags] 
	@ID INT = NULL,
	@Name NVARCHAR(50) = NULL
AS
BEGIN

	SELECT
	*
	FROM
	[Tags] T
	WHERE
		(	T.[ID] = @ID OR 
			@ID IS NULL OR 
			@ID = 0
		) AND
		
		(	T.[Name] = @Name OR 
			@Name IS NULL
		)
END

GO

CREATE PROCEDURE [GetTagsInNews]
	@NewsID INT
AS
BEGIN
	
	SELECT
	
	[TagsBind].TagID,
	[Tags].Name
	
	FROM 
	
	[TagsBind] inner join 
	[Tags] on Tags.ID = TagsBind.TagID
	
	WHERE
		NewsID = @NewsID

END

GO

CREATE PROCEDURE [GetUserImages]
	@ID INT = NULL,
	@UserID INT = NULL
AS
BEGIN
	
	SELECT
		*
	FROM
	[ImageUsersBind] I
	WHERE
	(	I.[UserID] = @UserID OR
		@UserID IS NULL
	) AND
	
	(	I.[ID] = @ID OR
		@ID IS NULL
	)
	ORDER BY
		Name
END

GO

CREATE PROCEDURE [GetUserImagesUrl]
	@UserID INT
AS
BEGIN
	
	SELECT
		Url
	FROM
		[ImageUsersBind]
	WHERE
		UserID = @UserID
END

GO

CREATE PROCEDURE [GetUsers] 
	@ID INT = NULL,
	@Login NVARCHAR(50) = NULL,
	@Role INT = NULL,
	@orderBy NVARCHAR(50) = 'login'
AS
BEGIN
	
	SELECT
	*
	FROM 
	[users] U
	WHERE
		(	U.[Login] = @Login OR 
			@Login IS NULL
		) AND
		
		(	U.[ID] = @ID OR 
			@ID IS NULL OR 
			@ID = 0
		)AND
		
		(	U.[Role] = @Role OR 
			@Role IS NULL OR 
			@Role = 0
		)
	ORDER BY CASE WHEN @orderby = 'login' THEN [Login] END,
			 CASE WHEN @orderBy = 'ID' THEN [ID]	END,
			 CASE WHEN @orderBy = 'Email' THEN [Email] END,
			 CASE WHEN @orderBy = 'Role' THEN [Role] END,
			 CASE WHEN @orderBy = 'Name' THEN [Name] END,
			 CASE WHEN @orderBy = 'FName' THEN [FName] END,
			 CASE WHEN @orderBy = 'MName' THEN [MName] END
	
	
END

GO



CREATE PROCEDURE .[PublishNews] 
	@ID INT
AS
BEGIN
	

	UPDATE 
	[News] 
	SET
		PublishDate=GETDATE()
	WHERE
		ID = @ID 
END

GO

CREATE PROCEDURE [ReceivePrivateMessage] 
	@ReceiverID INT = NULL,
	@ID INT = NULL
AS
BEGIN
	
	SELECT
		*
	FROM
	[PrivateMessage] P
	WHERE
		(	P.[ID] = @ID OR 
			@ID IS NULL
		) AND
		
		(	P.[ReceiverID] = @ReceiverID OR 
			@ReceiverID IS NULL
		)
	ORDER BY
		SendDate DESC
	
END

GO

CREATE PROCEDURE [Search] 
	@SearchString NVARCHAR(100)
AS
BEGIN

	SELECT 
		* 
	FROM 
		[News]
	WHERE
		Text  LIKE '%'+@searchString+'%' 
		or
		Title LIKE '%'+@searchString+'%'
END

GO

CREATE PROCEDURE [SetNewsImageBind]
	@NewsID INT,
	@Name NVARCHAR(100),
	@Url NVARCHAR (300)
AS
BEGIN

	SET @Url = @Url+CONVERT(NVARCHAR(10), @NewsID)+'/'+@Name

	INSERT INTO
		[ImageNewsBind]
	(	
		NewsID,
		Name,
		Url
	)
	VALUES
	(
		@NewsID,
		@Name,
		@Url
	)
		
	
END

GO

CREATE PROCEDURE [SetUsersVotes]
	@Login NVARCHAR(50),
	@NewsID INT,
	@Rating INT
AS
BEGIN

	DECLARE @UserID INT
	SET @UserID = (
				SELECT
					ID
				FROM
					[Users]
				WHERE
					Login = @Login
				)
	
	INSERT INTO
	[UsersVotes]
	(
		NewsID,
		UserID,
		Rating
	)
	VALUES
	(
		@NewsID,
		@UserID,
		@Rating
	)
END

GO

CREATE PROCEDURE [SetRating]
	@NewsID INT,
	@Rating INT,
	@Login NVARCHAR(50)
AS
BEGIN
	
	DECLARE @RatingNews DECIMAL(2,1)
	SET @RatingNews = (
		SELECT 
			Rating
		FROM
			[NewsRating]
		WHERE
			NewsID = @NewsID
	) 
	
	DECLARE @Votes INT
	SET @Votes = (
	SELECT 
		Votes
	FROM 
		[NewsRating]
	WHERE
		NewsID = @NewsID
	)
	
	IF @Votes IS NULL
	BEGIN
		INSERT INTO
		[NewsRating]
		(
			NewsID,
			Rating,
			Votes
		)
		VALUES
		(
			@NewsID,
			@Rating,
			1
		)
	END
	
	IF @Votes IS NOT NULL
	BEGIN
		UPDATE
		[NewsRating]
		SET
		Rating = (@RatingNews*@Votes+@Rating)/(@Votes+1),
		Votes = @Votes+1
	END
	
	EXEC SetUsersVotes @Login, @NewsID, @Rating
END

GO

CREATE PROCEDURE [SetUserImageBind]
	@UserID INT,
	@Name NVARCHAR(200),
	@Url NVARCHAR(300)
AS
BEGIN

	SET @Url = @Url+CONVERT(NVARCHAR(10), @UserID)+'/'+@Name

	DECLARE @ID INT
	SET @ID =	(
					SELECT
						ID
					FROM
						[ImageUsersBind]
					WHERE
						UserID = @UserID
				)
				
	IF @ID IS NOT NULL
	BEGIN
	
		UPDATE
			[ImageUsersBind]
		SET
			Name = @Name,
			Url = @Url
		WHERE
			UserID = @UserID
			
	END
	ELSE
	BEGIN
	
		INSERT INTO
			[ImageUsersBind]
		(
			UserID,
			Name,
			Url
		)
		VALUES
		(
			@UserID,
			@Name,
			@Url
		)
		
	END
	
END


GO

CREATE PROCEDURE [UpdateCategory]
	@ID INT,
	@Name NVARCHAR(50),
	@Description NVARCHAR(MAX) 
AS
BEGIN
	
	
	UPDATE 
	[Category]
	SET	
		Name=@Name,
		Description=@Description
	WHERE 
		ID = @ID	
	

END

GO

CREATE PROCEDURE [UpdateLastVisit]
	@ID INT = NULL,
	@Login NVARCHAR(50) = NULL
AS
BEGIN
	UPDATE
	[Users]
	SET
		LastVisit = GETDATE()
	WHERE
		(	Users.[ID] = @ID OR 
			@ID IS NULL
		) AND
		
		(	Users.[Login] = @Login OR 
			@Login IS NULL
		)
END

GO

CREATE PROCEDURE [UpdateNews] 
		@Title NVARCHAR(50), 
	@Category INT,
	@Author NVARCHAR(50),
	@Text NVARCHAR(MAX),
	@ID INT,
	@Rating DECIMAL(2,1)
	

AS
BEGIN
	
	UPDATE 
		[News]
	SET 
		Title=@Title,
		Category=@Category,
		Author=@Author,
		Text=@Text,
		UpdateDate=GETDATE()
	
	WHERE 
		ID=@ID
		
	UPDATE
		[NewsRating]
	SET
		Rating=@Rating
	WHERE
		NewsID = @ID
	
END


GO



CREATE PROCEDURE .[UpdateTag]
	@ID INT,
	@Name NVARCHAR(50)
AS
BEGIN
	

	UPDATE 
	[Tags] 
	SET
		Name =@Name
	WHERE
		ID=@ID 
END

GO

CREATE PROCEDURE [UpdateUser] 
	@Login NVARCHAR(50), 
	@Role INT = 3,
	@Pwd NVARCHAR(50),
	@Email NVARCHAR(60),
	@Name NVARCHAR(50),
	@FName NVARCHAR(50) = NULL,
	@MName NVARCHAR(50) = NULL,
	@ID INT

AS
BEGIN

	UPDATE 
	[Users] 
	SET 
		Login=@Login,
		Role=@Role,
		Pwd=@Pwd,
		Email=@Email,
		Name=@Name,
		FName=@FName,
		MName=@MName
	
	WHERE 
		ID=@ID
END

GO

CREATE PROCEDURE [VoteOrNot] 
	@Login NVARCHAR(50),
	@NewsID INT
AS
BEGIN
	DECLARE @UserID INT
	SET @UserID =(
					SELECT 
						ID 
					FROM 
						[Users] 
					WHERE 
						Login=@Login
				  )
	
	SELECT
		*
	FROM
	[UsersVotes]
	WHERE
		NewsID = @NewsID 
		AND
		UserID = @UserID
	
END

GO

INSERT INTO
[Users]
(
	Login,
	Name,
	Pwd,
	Email,
	Role,
	LastVisit
)
VALUES
(
	'Admin',
	'Admin',
	'111111',
	'test@mail.ru',
	1,
	GETDATE()
)